SQL Server: Optimizing Stored Procedure Performance

If you want to use SQL Server databases effectively, you will end up using stored procedures. Their aim is that you have optimized and compiled code that resides in a cache to improve workload performance for subsequent executions. However, you might find that it does not always work out that way. The performance of a stored procedure is heavily dependent on how the plan is chosen and cached. If the plan is not optimal for subsequent executions it could cause performance to suffer greatly. In the end, you might suffer from what is known as parameter-sniffing-problems where the optimal plan for a procedure varies based on the parameters supplied. This comprehensive course will teach you how stored procedure plan caching works and how to get SQL Server to produce the optimal plan for your stored procedure performance in a variety of circumstances. The course is applicable to SQL Server developers and anyone who is responsible for writing stored procedures that must repeatedly perform well. The demo database provided is compatible with SQL Server 2008 through SQL Server 2014. All course demos are shown on SQL Server 2014 with references to behaviors (where different) on all versions. Optimization strategies you will learn in the course apply to SQL Server 2005 onward, and some even back to SQL Server 2000.

Introduction	00:21:19
Introduction	01:51
This Course	02:35
What Does Optimizing Stored Procedure Performance Mean?	02:49
Does This Sound Familiar?	02:11
What Does Optimizing Stored Procedure Performance NOT Mean?	03:46
Why This Course Is Relevant	03:11
Course Focus and Structure (1)	02:09
Course Focus and Structure (2)	02:44
Why Use Stored Procedures?	00:27:04
Overview	02:04
Different Ways to Execute SQL Statements	02:25
Some Statements Can Be Cached for Reuse (1)	01:45
Some Statements Can Be Cached for Reuse (2)	03:43
Version-Specific Plan Caching Whitepapers	01:26
Reducing Plan Cache Pollution	04:32
Understanding sp_executesql	02:51
Stored Procedures / sp_executesql and the Cache	02:39
Parameter Sniffing	02:01
Summary: Why Use Stored Procedures?	03:34
Creation, Compilation, and Invalidation Section 1	01:08:12
Overview	02:15
What Happens When You Create a Procedure?	03:22
Where Are Stored Procedures Stored? (1)	01:53
Where Are Stored Procedures Stored? (2)	01:38
Creating Stored Procedures	02:47
Stored Procedure Plans and Caching	05:00
Side Effect: Plan Cache Flush (1)	02:35
Side Effect: Plan Cache Flush (2)	01:20
Side Effect: Plan Cache Flush (3)	03:09
Side Effect: Plan Cache Flush (4)	02:37
DEMO: Generic Demo Intro	01:43
DEMO: Setup Credit Sample Database	05:20
DEMO: Setup Analysis Procedures for Credit	07:35
DEMO: Object-Level Invalidation or Eviction	16:11
DEMO: Database-Level Invalidation or Eviction	03:43
DEMO: Server-Level Invalidation or Eviction	06:55
Creation, Compilation, and Invalidation Section 2	00:53:29
Plan Invalidation	05:09
Plan Invalidation Due to Statistics Updates	04:16
DEMO: Plan Invalidation and Statistics - Part 1	14:23
Updates to Statistics may not Invalidate Bad Plans	03:01
DEMO: Plan Invalidation and Statistics - Part 2	13:30
Plan Invalidation / Recompilation Causes	02:31
Stored Procedure Caching + Compilation Concerns	04:06
When Should You Recompile?	03:45
Summary: Creation and Compilation	02:45
Optimization and Recompilation Section 1	00:55:44
Overview	04:24
Options for Recompilation	04:11
sp_recompile object_name	06:52
DEMO: Long Blocking Chains	11:13
CREATE ... WITH RECOMPILE	03:21
DEMO: CREATE ... WITH RECOMPILE	05:22
Conditional Logic	02:49
DEMO: Conditional Logic - Part 1	08:43
Modularization	03:17
DEMO: Conditional Logic - Part 2 - Modularization	05:27
Optimization and Recompilation Section 2	00:51:43
EXECUTE ... WITH RECOMPILE (1)	03:38
DEMO: EXECUTE ... WITH RECOMPILE For Testing	10:18
EXECUTE ... WITH RECOMPILE (2)	03:39
DEMO: Determining Plan Stability	07:44
Statement-Level Recompilation	05:33
DEMO: Recompilation With OPTION (RECOMPILE)	04:15
DEMO: Recompilation With OPTION (Optimize For Literal)	04:23
DEMO: Recompilation With OPTION (Optimize For UNKNOWN)	09:17
DEMO: Variables Simulate Optimize For UNKNOWN	02:51
Optimization and Recompilation Section 3	01:08:24
OPTION (RECOMPILE)	03:56
OPTIMIZE FOR ...	04:41
DEMO: Scenario - Setup	04:15
DEMO: Scenario - Scenario Explained	05:26
DEMO: Scenario - Conditional Logic Does NOT Work	05:46
DEMO: Scenario - EXEC With RECOMPILE	07:20
DEMO: Scenario - Conditional Logic Plus Modularization WORKS!	06:32
DEMO: Stabilizing Plans With Indexes - Setup and Scenario	04:36
DEMO: Stabilizing Plans With Indexes - Plan Stability	10:23
DEMO: Stabilizing Plans With Indexes - Voila!	10:17
Summary: Walkthrough Demo (1)	02:20
Summary: Walkthrough Demo (2)	02:46
Optimization and Recompilation Section 4	01:02:20
When Does a Procedure Get Optimized?	03:39
Server-Wide: OPTIMIZE FOR UNKNOWN	03:08
The Checkered Past of OPTION (RECOMPILE)	03:37
Dynamic String Execution	03:09
DEMO: Dynamic String Execution	08:04
Multi-Purpose Procedures	02:41
Building Strings Dynamically and Caching	03:44
DEMO: Multi-Purpose Procedures - Setup and Scenario	05:21
DEMO: Multi-Purpose Procedures - The Problem	05:08
DEMO: Multi-Purpose Procedures - Option Recompile too Much CPU	03:15
DEMO: Multi-Purpose Procedures - The Ultimate Multi-Purpose Proc	16:47
Patterns and Practices in Statement Recompilation	02:59
Summary: Stored Procedure Pitfalls/Performance	00:43
Other Concerns and Considerations	00:23:17
Overview	03:46
Demystifying Plan Caching for Bad Plans	03:04
Secondary Concerns and Considerations	07:57
Optimizing Statement and Procedure Performance	02:35
Just the Tip of the Iceberg	04:19
Course Summary	01:33